<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

/**
 * DBSync Class syncs your application database with the config/db_sync.xml file
 *
 * This class will allow you to compare the contents of config/db_sync.xml with
 * the actual schema of your database.  This will list any differnces between the
 * file and the database, generate queries to correct those differences, and
 * allow you to run the queries to process the changes.
 *
 * If there is no config/db_schema.xml file, this class will be quietly die
 * (in case you don't want to use the dbsync features of CIHeadstart)
 *
 * @author Casey McLaughlin
 * @package CIHeadStart
 * @link http://code.google.com/p/ciheadstart
 */
class Dbsync extends MY_Library
{
	private $ci;
	private $driver_class;
	private $field_attributes = array('type' => 'varchar',
																		'constraint' => '100',
																		'unsigned' => 'true',
																		'default' => '',
																		'primary_key' => 'false',
																		'null' => 'true',
																		'zerofill' => 'false',
																		'auto_increment' => 'false');

	private $last_sync_result = NULL;


	// --------------------------------------------------------------------

	/**
	 * Constructor
	 *
	 * Parameter options:
	 *   - db_group_name = Which database to use (configured in config/databases.php)
	 *
	 * @param array
	 */
	public function __construct($params = NULL)
	{
		//Get the paramaters
		if (is_array($params))
		{
			if (isset($params['db_group_name']))
				$db_group_name = $params['db_group_name'];
			else
				$db_group_name = NULL;
		}
		else
		{
			$db_group_name = NULL;
		}

		//Get an instance of CI or die a horrible death
		if ( ! $this->ci =& get_instance())
			trigger_error("Could not load the CI library in the dbsync class", E_USER_ERROR);

		//Get the database to connect to if there is one specified
		if ($db_group_name !== NULL)
			$this->ci->load->database($db_group_name);
		else
			$this->ci->load->database();

		//Load the driver class or UBER fail!
		if (is_readable(APPPATH . 'libraries/Dbsync_drivers/dbsync_' . $this->ci->db->dbdriver . '.php'))
		{
			require(APPPATH . 'libraries/Dbsync_drivers/dbsync_' . $this->ci->db->dbdriver . '.php');
			$classname = "Dbsync_" . $this->ci->db->dbdriver;
			$this->driver_class = new $classname($this->ci->db);
		}
		else
			trigger_error("The DBSync library does not (yet) support your database driver: " . $this->ci->db->dbdriver, E_USER_ERROR);

		//Load the dbforge class..
		$this->ci->load->dbforge();
	}

	// --------------------------------------------------------------------

	/**
	 * Get the overall result of the last actual synchronization
	 *
	 * Returns TRUE if all items succeeded
	 * Returns FALSE if some or all items failed
	 * Returns NULL if no synchronization has been performed
	 *
	 * @return boolean|null
	 */
	public function get_last_sync_result()
	{
		return $this->last_sync_result;
	}

	// --------------------------------------------------------------------

	/**
	 * Perfom db & file sync
	 * 
	 * This commits the changes generated in $this->get_db_schema_changes() to
	 * the database.  You can define changes to skip by array key (integers) or
	 * by change_types.
	 *
	 * @param array $changes
	 * @param array|null $skip_ids
	 * @param array|null $skip_change_types
	 * @return array
	 */
	public function sync_db_and_file($changes = NULL, $skip_ids = NULL, $skip_change_types = NULL)
	{
		//If no changes sent, then get the changes automatically
		if ( ! is_array($changes))
			$changes = $this->get_db_schema_changes();

		if ( ! is_array($skip_ids))
			$skip_ids = array();

		if ( ! is_array($skip_change_types))
			$skip_change_types = array();

		//If changes still isn't an array... die
		if ( ! is_array($changes))
			return $this->_do_error('Could not sync the database and the file.  Error getting the db schema changes.');

		//Reset last_sync_result - Important: this goes right before the loop
		$this->last_sync_result = NULL;
		$out_changes = array();
		foreach($changes as $change_id => $in_change_obj)
		{
			$change_obj = $in_change_obj;

			if (in_array($change_obj->type, $skip_change_types))
				$change_obj->action_result = "skipped because you requested that all changes of type '$change_obj->type' are ignored";
			elseif (in_array($change_id, $skip_ids))
				$change_obj->action_result = "skipped because you requested that this item ($change_id) is ignored";
			else  //check to see if it's okay, then perform the synchronization
			{
				$okay_to_go = FALSE; //not okay until we prove it's okay!
				
				//if the type is a field_drop, then make sure the field is empty in the table before dropping it
				if ($change_obj->type == 'field_drop')
				{
					//check to see if the field has values in the database.
					//If not, okay to go.. if so then action_result = skipped because the field is not empty
					if ($this->ci->db->query("SELECT * FROM $change_obj->table WHERE $change_obj->field <> '';")->num_rows() == 0)
						$okay_to_go = TRUE;
					else
					{
						$okay_to_go = FALSE;
						$change_obj->action_result = 'skipped because the field is not empty.';
					}
				}
				//if the type is a table_drop, then make sure the table is empty before dropping it
				elseif ($change_obj->type == 'table_drop')
				{
					//check to see if the table has rows in the database
					//If not, okay to go.. if so then action_result = skipped because the table is not empty
					if ($this->ci->db->count_all($change_obj->table) == 0)
						$okay_to_go = TRUE;
					else
					{

						$okay_to_go = FALSE;
						$change_obj->action_result = 'skipped because the table is not empty.';
					}	
				}
				else //all other types are okay to go
					$okay_to_go = TRUE;

				if ($okay_to_go) //go ahead and try the update
				{
					$db_result = $this->ci->db->query($change_obj->sql_query);
					if ($db_result)
					{
						$change_obj->action_result = 'success';
						$change_obj->action_succeeded = TRUE;
					}
					else
					{
						$change_obj->action_result = 'failed! Database error: ' . $this->get_db_error();
						$change_obj->action_succeeded = FALSE;
						$this->last_sync_result = FALSE;
					}
				}
			}

			$out_changes[$change_id] = $change_obj;
			unset($change_obj);
		}

		if ($this->last_sync_result === NULL)
			$this->last_sync_result = TRUE;

		//Return the changes with the results
		return $out_changes;
	}

	// --------------------------------------------------------------------

	/**
	 * Returns a list of SQL queries to make the database match
	 * the file schema defined in db_schema.xml
	 *
	 * ...is _changes() the right word?  Should it be differences() ?
	 *
	 * @return array
	 */
	public function get_db_schema_changes()
	{
		$changes = array();

		$differences = $this->compare_schemas();

		foreach($differences->removals as $table_name => $table_data)
		{
			$change->sql_query = $this->driver_class->generate_db_drop_query($table_name);
			$change->explanation = "The table '$table_name' exists in the database, but does not exist in the schema file.";
			$change->change_action = "Perform DROP query to remove table '$table_name' from database";
			$change->table = $table_name;
			$change->type = 'table_drop';
			
			$changes[] = $change;
			unset($change);
		}

		foreach($differences->additions as $table_name => $table_data)
		{
			$change->sql_query = $this->driver_class->generate_db_create_query($table_name, $table_data);
			$change->explanation = "The table '$table_name' exists in the schema file, but not in the database.";
			$change->change_action = "Perform CREATE TABLE query to add table '$table_name' to database";
			$change->table = $table_name;
			$change->type = 'table_create';

			$changes[] = $change;
			unset($change);
		}

		foreach($differences->modifications as $table_name => $table_modifications)
		{
			//the $table_modifications will consist of:
			// additions (array)
			// removals (array)
			// modifications (array)

			//foreach - each of these.. run the driver class and create a change object
			foreach($table_modifications->additions as $field_name => $field_attrs)
			{
				$change->sql_query = $this->driver_class->generate_db_alter_query($table_name, 'add', $field_name, $field_attrs);
				$change->explanation = "The field '$field_name' in table '$table_name' exists in the schema file, but not in the database.";
				$change->change_action = "Perform ALTER TABLE query to add field '$field_name' in the the table '$table_name' to database";
				$change->table = $table_name;
				$change->field = $field_name;
				$change->type = 'field_create';

				$changes[] = $change;
				unset($change);
			}

			foreach($table_modifications->removals as $field_name => $field_attrs)
			{
				$change->sql_query = $this->driver_class->generate_db_alter_query($table_name, 'remove', $field_name, $field_attrs);
				$change->explanation = "The field '$field_name' in table '$table_name' exists in the database, but not in the schema file.";
				$change->change_action = "Perform ALTER TABLE query to drop field '$field_name' in the table '$table_name' from database";
				$change->table = $table_name;
				$change->field = $field_name;
				$change->type = 'field_drop';

				$changes[] = $change;
				unset($change);
			}

			foreach($table_modifications->modifications as $field_name => $field_attrs)
			{
				$change->sql_query = $this->driver_class->generate_db_alter_query($table_name, 'update', $field_name, $field_attrs);
				$change->explanation = "The field '$field_name' in table '$table_name' in the database does not match the schema file.";
				$change->change_action = "Perform ALTER TABLE to change field '$field_name' in the table '$table_name' in the database";
				$change->table = $table_name;
				$change->field = $field_name;
				$change->type = 'field_change';

				$changes[] = $change;
				unset($change);
			}
		}


		foreach($differences->records as $table_name => $record_changes)
		{
			foreach($record_changes as $rec_to_add)
			{
				$change->sql_query = $this->driver_class->generate_db_insert_query($table_name, $rec_to_add);
				$change->explanation = "The table '$table_name' is missing a persistent or default record defined in the file schema.";
				$change->change_action = "Perform INSERT query to add the record with values [" . implode(', ', (array) $rec_to_add) . "] to the table '$table_name' in the database";
				$change->table = $table_name;
				$change->type = 'record_add';

				$changes[] = $change;
				unset($change);
			}
		}

		return $changes;
	}

	// --------------------------------------------------------------------

	/**
	 * Read the database schema into a standard
	 * data format for this class
	 *
	 * @return array
	 */
	public function read_db_schema()
	{
		if ($this->ci->db->dbdriver == 'mysql')
			return $this->driver_class->read_db_schema();
		else
			return $this->_do_error("Currently, your database driver (" . $this->ci->db->dbdriver . ") is not supported by dbsync!");
	}

	// --------------------------------------------------------------------

	public function check_schema_exists()
	{
		if (is_readable(APPPATH . 'config/db_schema.xml') OR is_readable(APPPATH . 'config/db_schema.js'))
			return TRUE;
		else
			return FALSE;
	}

	// --------------------------------------------------------------------

	/**
	 * Read the XML file schema into a standard
	 * data format for this class.
	 *
	 * By the way.. db table prefixes (configured in CI) are appended automatically
	 * in this function so that they match the database names
	 *
	 * @return array
	 */
	public function read_file_schema()
	{
		$this->ci->load->helper('file');

		if (is_readable(APPPATH . 'config/db_schema.xml'))
			$data = $this->read_file_schema_xml(read_file(APPPATH . 'config/db_schema.xml'));
		else
			return $this->_do_error("Could not read the db_schema file in the config directory");

		//Foreach table, add the prefix if it's not already there.
		$the_prefix = $this->ci->db->dbprefix;
		if (strlen($the_prefix) > 0)
		{
			$out_tables = array();
			foreach($data->tables as $tbl_name => $tbl_content)
			{
				if (substr($tbl_name, 0, strlen($the_prefix)) != $the_prefix)
					$out_tables[$the_prefix . $tbl_name] = $tbl_content;
				else
					$out_tables[$tbl_name] = $tbl_content;
			}
			$data->tables = $out_tables;
		}

		//Fix artifacts for reading from the file schema
		foreach($data->tables as $table_name => &$table)
		{
			foreach($table->fields as $field_name => &$field_attr)
			{
				if ($field_attr->type == 'varchar' && ( ! isset($field_attr->constraint) OR $field_attr->constraint == ''))
					$field_attr->constraint = '50';
			}
		}

		return $data;
	}

	// --------------------------------------------------------------------

	/**
	 * Read a db_schema XML file, and return an array of objects
	 *
	 * @param string $file_contents
	 * @return array
	 */

	private function read_file_schema_xml($file_contents)
	{
		//Attempt to read the whole file into a SimpleXMLElement
		if ($xml_data = new SimpleXMLElement($file_contents))
		{
			//If there are tables in this databae...
			if (count($xml_data->tables->table) > 0)
			{
				$tables_array = array(); //each table will be an object in an array

				//Foreach table in the XML file...
				foreach($xml_data->tables->table as $table)
				{
					//Ensure that the table contains the "name" attribute
					if ( ! isset($table['name']))
						return $this->_do_error("Error reading the db_schema file.  There is a table node without the name 'attribute'!");
					else
						$table_name = (string) $table['name'];

					//Ensure there are fields, also.  They are required!
					if (count($table->field) < 1)
						return $this->_do_error("Error reading the db_schema file.  Table '$table_name' does not contain any fields");

					$curr_table = (object) NULL;
					
					//--
					// 1. Get all of the fields
					$curr_table->fields = array();
					foreach($table->field as $field)
					{
						//Ensure the 'name' attribute exists
						if ( ! isset($field['name']))
							return $this->_do_error("Error reading the db_schema file.  A field in the table '$table_name' is missing the required attribute 'name'");
						else
							$field_name = (string) $field['name'];

						//Ensure the 'type' attribute exists and 'constraint' if it is a varchar
						if ( ! isset($field['type']))
							return $this->_do_error("Error reading the db_schema file.  The field '$field_name' in table '$table_name' is missing the required attribute 'type'");
						elseif (strtolower($field['type']) == 'varchar' && ! isset($field['constraint']))
							return $this->_do_error("Error reading the db_schema file.  The field '$field_name' in table '$table_name' is of type 'varchar', but is missing the required attribute 'constraint'");

						$field_attrs = (object) NULL;
						foreach($this->field_attributes as $field_attr => $field_attr_default)
						{
							if (isset($field[$field_attr]))
								$field_attrs->$field_attr = (string) $field[$field_attr];
						}

						$curr_table->fields[$field_name] = $field_attrs;
						unset($field_attrs);
					}

					//--
					// 2. Get any records that should always be in the table
					$curr_table->persistent_records = array();
					if (isset($table->persistent_records->record) && count($table->persistent_records->record) > 0)
					{
						foreach($table->persistent_records->record as $record)
						{
							$out_record = (object) NULL;
							foreach($record->attributes() as $attr_name => $attr_value)
							{
								$attr_name = (string) $attr_name;
								$attr_value = (string) $attr_value;

								if ( ! in_array($attr_name, array_keys($curr_table->fields)))
									return $this->_do_error("Error reading the db_schema file.  A persistent record contains the field '$attr_name', which doesn't exist in the table.");
								else
									$out_record->$attr_name = $attr_value;
							}

							$curr_table->persistent_records[] = $out_record;
							unset($out_record);
						}
					}

					//--
					// 3. Get any records that should go in if the table is empty
					$curr_table->default_records = array();
					if (isset($table->default_records->record) && count($table->default_records->record) > 0)
					{
						foreach($table->default_records->record as $record)
						{
							$out_record = (object) NULL;
							foreach($record->attributes() as $attr_name => $attr_value)
							{
								$attr_name = (string) $attr_name;
								$attr_value = (string) $attr_value;

								if ( ! in_array($attr_name, array_keys($curr_table->fields)))
									return $this->_do_error("Error reading the db_schema file.  A default record contains the field '$attr_name', which doesn't exist in the table.");
								else
									$out_record->$attr_name = $attr_value;
							}

							$curr_table->default_records[] = $out_record;
							unset($out_record);
						}
					}
					
					//Add the whole table (fields, record, etc.) to the array
					$tables_array[$table_name] = $curr_table;
					unset($curr_table);
				}
			}
			else
				$tables_array = array();

			//Done going through tables getting the data..

			//See if there are any tables in the ignore list..
			$ignore_list = array();
			if (isset($xml_data->ignore_tables->table) && count($xml_data->ignore_tables->table) > 0)
			{
				foreach($xml_data->ignore_tables->table as $ignore_table)
				{
					if (isset($ignore_table['name']))
						$ignore_list[] = (string) $ignore_table['name'];
				}
			}

			$out_obj = (object) NULL;
			$out_obj->tables = $tables_array;
			$out_obj->ignored_tables = $ignore_list;

			//Return the whole thing
			return $out_obj;
		}
		else
			return $this->_do_error("Could not read the db_schema file.  It does not contain valid XML!");
	}

	// --------------------------------------------------------------------

	/**
	 * Returns only the total number of differences
	 *
	 * @return int
	 */
	public function get_total_number_of_differences()
	{
		return $this->get_number_of_differences()->total;
	}

	// --------------------------------------------------------------------

	/**
	 * Get the number of difference in the database
	 *
	 * @return object;
	 */
	 public function get_number_of_differences()
	 {
		 $diffs = $this->compare_schemas();

		 $obj = (object) NULL;
		 $obj->additions = count($diffs->additions);
		 $obj->removals = count($diffs->removals);

		 
		 $obj->modifications_additions = 0;
		 $obj->modifications_removals = 0;
		 $obj->modifications_updates = 0;

		 foreach($diffs->modifications as $table => $changes)
		 {
			 $obj->modifications_additions += count($changes->additions);
			 $obj->modifications_removals += count($changes->removals);
			 $obj->modifications_updates += count($changes->modifications);
		 }

		 $obj->record_additions = 0;

		 foreach($diffs->records as $table => $recadds)
			 	$obj->record_additions += count($recadds);

		$obj->modifications_total = $obj->modifications_additions + $obj->modifications_removals + $obj->modifications_updates + $obj->record_additions;

		 $obj->total = $obj->additions + $obj->removals + $obj->modifications_total;

		 return $obj;
	 }

	// --------------------------------------------------------------------

	/**
	 * Compare the schemas and return a complex object/array that
	 * shows which fields match and which are new
	 *
	 * @return array
	 */
	public function compare_schemas()
	{
		$db_table_adds = array();
		$db_table_diffs = array();
		$db_table_removals = array();
		$db_record_adds = array();

		//Get the file schema
		if ( ! $file_schema = $this->read_file_schema() OR ( ! is_array($file_schema->tables)))
			return $this->_do_error("Could not compare schemas, because the file schema could not be read.");

		//Get the database schema
		if ( ! $db_schema = $this->read_db_schema() OR ( ! is_array($db_schema->tables)))
			return $this->_do_error("Could not compare schemas, because the database schema could not be read.");

		//Remove the ignored tables
		foreach($db_schema->tables as $tbl_name => $tbl_values)
		{
			if (in_array($tbl_name, $file_schema->ignored_tables))
				unset($db_schema->tables[$tbl_name]);
		}

		//First, see which tables are in the file, but not in the db.. these are additions
		foreach($file_schema->tables as $tbl_name => $tbl_content)
		{
			if ( ! in_array($tbl_name, array_keys($db_schema->tables)))
				$db_table_adds[$tbl_name] = $tbl_content;
		}

		//Next, see which tables are in the database, but not the file.. these are deletions
		foreach($db_schema->tables as $tbl_name => $tbl_content)
		{
			if ( ! in_array($tbl_name, array_keys($file_schema->tables)))
				$db_table_removals[$tbl_name] = $tbl_content;
		}

		//Finally, go through the intersection of the two arrays and check the field diffs..
		//The file will always win ... also check the records
		$tables_in_both = array_intersect(array_keys($file_schema->tables), array_keys($db_schema->tables));
		foreach($tables_in_both as $tbl_name)
		{
			$db_table = $db_schema->tables[$tbl_name];
			$file_table = $file_schema->tables[$tbl_name];

			$this_table_field_adds = array();
			$this_table_field_dels = array();
			$this_table_field_mods = array();
			
			//First, see which fields are in the file, but not in the db.. these are additions
			foreach($file_table->fields as $field_name => $field_attr)
			{
				if ( ! isset($db_table->fields[$field_name]))
					$this_table_field_adds[$field_name] = $field_attr;
			}

			//Next, see which fields are in the database, but not in the file. these are deletions
			foreach($db_table->fields as $field_name => $field_attr)
			{
				if ( ! isset($file_table->fields[$field_name]))
					$this_table_field_dels[$field_name] = $field_attr;
			}

			//Finally, see which fields are in both the database and the file file.  Check for
			//differences.
			$fields_in_both = array_intersect(array_keys($file_table->fields), array_keys($db_table->fields));
			foreach($fields_in_both as $field_name)
			{
				$db_field_attrs = (array) $db_table->fields[$field_name];
				$file_field_attrs = (array) $file_table->fields[$field_name];

				//If there are attributes in the database, but not in the file, don't worry about them...
				//If there are attributes in the file, but not in the database, we'll need to modify
				//If the attributes are in both but different, we'll need to modify
				$modify = FALSE;
				foreach($file_field_attrs as $attr_name => $attr_value)
				{
					if ( ! isset($db_field_attrs[$attr_name]) OR $db_field_attrs[$attr_name] != $file_field_attrs[$attr_name])
					{
						$modify = TRUE;
						break;
					}
				}

				if ($modify)
					$this_table_field_mods[$field_name] = $file_table->fields[$field_name];
			}

			//Put them all together
			$this_table_diffs = (object) NULL;
			$this_table_diffs->additions = $this_table_field_adds;
			$this_table_diffs->removals = $this_table_field_dels;
			$this_table_diffs->modifications = $this_table_field_mods;

			//Add the final to the table array
			if (count($this_table_diffs) > 0)
				$db_table_diffs[$tbl_name] = $this_table_diffs;

			if (isset($file_table->persistent_records) && count($file_table->persistent_records) > 0)
			{
				foreach($file_table->persistent_records as $prec)
				{
					$where_clause = (array) $prec;
					$num_records = @$this->ci->db->get_where($tbl_name, $where_clause)->num_rows();

					if ($num_records == 0)
						$db_record_adds[$tbl_name][] = $prec;

					unset($prec, $where_clause);
				}
			}

			if (isset($file_table->default_records) && count($file_table->default_records) > 0)
			{
				$num_records = @$this->ci->db->count_all($tbl_name);

				if ($num_records == 0)
				{
					foreach($file_table->default_records as $prec)
					{
						$db_record_adds[$tbl_name][] = $prec;
						unset($prec);
					}
				}
			}

			unset($this_table_diffs, $db_table, $file_table, $fields_in_both);
		}

		//Setup the object...
		$diffs = (object) NULL;
		$diffs->removals = $db_table_removals;
		$diffs->additions = $db_table_adds;
		$diffs->modifications = $db_table_diffs;
		$diffs->records = $db_record_adds;

		//...and... return it!
		return $diffs;
	}

	/**
	 * Get the last error from the databases
	 *
	 * @return string
	 */
	public function get_db_error()
	{
		return $this->driver_class->get_db_error();
	}
}

// --------------------------------------------------------------------

class Dbsync_driver extends MY_Library {

	protected $db;

	public function __construct($db_object)
	{
		$this->db =& $db_object;
	}
}

/* End of file database_sync.php */
/* Location: ./app/libraries/database_sync.php */